/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mx.ipn.escuela.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import mx.ipn.escuela.modelo.Maestro;

/**
 *
 * @author Alejandro
 */
public class MaestroDao {

    public List<Maestro> lista() {
        List<Maestro> maestros = new ArrayList();
        Connection conn = null;
        try {
            conn = obtenerConexion();
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from maestros");
            while (rs.next()) {
                Maestro maestro = new Maestro();
                maestro.setId(rs.getLong("id"));
                maestro.setEmpleado(rs.getString("empleado"));
                maestro.setNombre(rs.getString("nombre"));
                maestro.setApellido(rs.getString("apellido"));
                maestro.setCorreo(rs.getString("correo"));
                maestros.add(maestro);
            }

        } catch (ClassNotFoundException e) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, e);
        } catch (SQLException e) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return maestros;
    }

    public Maestro obtiene(Long id) {
        Maestro maestro = null;
        Connection conn = null;
        try {
            conn = obtenerConexion();
            PreparedStatement ps =
                    conn.prepareStatement("select * from maestros where id = ?");
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                maestro = new Maestro();
                maestro.setId(rs.getLong("id"));
                maestro.setEmpleado(rs.getString("empleado"));
                maestro.setNombre(rs.getString("nombre"));
                maestro.setApellido(rs.getString("apellido"));
                maestro.setCorreo(rs.getString("correo"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return maestro;
    }

    public Maestro crea(Maestro maestro) {
        Connection conn = null;
        try {
            conn = obtenerConexion();
            PreparedStatement ps =
                    conn.prepareStatement("insert into "
                    + "maestros(empleado,nombre,apellido,correo)"
                    + "values(?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, maestro.getEmpleado());
            ps.setString(2, maestro.getNombre());
            ps.setString(3, maestro.getApellido());
            ps.setString(4, maestro.getCorreo());

            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                maestro.setId(rs.getLong(1));
            } else {
                throw new RuntimeException("No se pudieron obtener "
                        + "las llaves generadas");
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return maestro;
    }

    public Maestro actualiza(Maestro maestro) {
        Connection conn = null;
        try {
            conn = obtenerConexion();
            PreparedStatement ps =
                    conn.prepareStatement("update maestros "
                    + "set nombre = ?, apellido = ?, correo = ? where id = ?");
            ps.setString(1, maestro.getNombre());
            ps.setString(2, maestro.getApellido());
            ps.setString(3, maestro.getCorreo());
            ps.setLong(4, maestro.getId());

            ps.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
        return maestro;
    }

    public void elimina(Long id) {
        Connection conn = null;
        try {
            conn = obtenerConexion();
            PreparedStatement ps =
                    conn.prepareStatement("delete from maestros where id = ?");
            ps.setLong(1, id);

            ps.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MaestroDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
    }

    private Connection obtenerConexion() throws SQLException, ClassNotFoundException {
        Connection conn = null;
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///escuela", "ipn", "ipn00");
        return conn;
    }
}



